﻿using System.Collections.Generic;
using System.IO;
using System.Data.OleDb;
using System.Text;
using System.Data;

namespace BDShop.Framework.Utility
{
    /// <summary>
    /// Excel Reader
    /// http://www.codeproject.com/KB/miscctrl/Excel_data_access.aspx#data
    /// component: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734
    /// </summary>
    public class ExcelReader
    {
        private string excelObject = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2}; Extended Properties=\"Excel {3};HDR=YES\"";
        private string filepath = string.Empty;
        private OleDbConnection con = null;



        //ConnectionString
        private string ConnectionString
        {
            get
            {
                if (!string.IsNullOrWhiteSpace(filepath))
                {
                    //Check for File Format
                    FileInfo fi = new FileInfo(this.filepath);
                    if (fi.Extension.Equals(".xls"))
                    {
                        // For Excel Below 2007 Format
                        return string.Format(this.excelObject,
                                             "Jet", "4.0", this.filepath, "8.0");
                    }
                    else if (fi.Extension.Equals(".xlsx"))
                    {
                        // For Excel 2007 File  Format
                        return string.Format(this.excelObject,
                                             "Ace", "12.0", this.filepath, "12.0");
                    }
                }

                return string.Empty;

            }
        }
        //OleDbConnection to the current File
        public OleDbConnection Connection
        {
            get
            {
                if (con == null)
                {
                    OleDbConnection _con = new OleDbConnection
                    {
                        ConnectionString = this.ConnectionString
                    };
                    this.con = _con;
                }
                return this.con;
            }
        }


        public ExcelReader(string path)
        {

            this.filepath = path;
        }
        // Reads the Schema Information
        public DataTable GetSchema()
        {
            DataTable dtSchema = null;
            if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
            dtSchema = this.Connection.GetOleDbSchemaTable(
                OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            return dtSchema;
        }

        public DataTable ReadSheet(string sheetName)
        {
            return this.ReadTable(string.Format("{0}$", sheetName));
        }

        //Reads table and returns the DataTable
        public DataTable ReadTable(string tableName)
        {
            return this.ReadTable(tableName, "");
        }

        public DataTable ReadTable(string tableName, string criteria)
        {


            DataTable resultTable = null;
            if (this.Connection.State != ConnectionState.Open)
            {
                this.Connection.Open();

            }
            string cmdText = "Select * from [{0}]";
            if (!string.IsNullOrEmpty(criteria))
            {
                cmdText += " Where " + criteria;
            }
            OleDbCommand cmd = new OleDbCommand(string.Format(cmdText, tableName));
            cmd.Connection = this.Connection;
            OleDbDataAdapter adpt = new OleDbDataAdapter(cmd);

            DataSet ds = new DataSet();

            adpt.Fill(ds, tableName);

            if (ds.Tables.Count == 1)
            {
                return ds.Tables[0];
            }
            else
            {
                return null;
            }

        }
        //Generates DropTable statement and executes it.
        public bool DropTable(string tablename)
        {


            if (this.Connection.State != ConnectionState.Open)
            {
                this.Connection.Open();

            }
            string cmdText = "Drop Table [{0}]";
            using (OleDbCommand cmd = new OleDbCommand(
                string.Format(cmdText, tablename), this.Connection))
            {

                cmd.ExecuteNonQuery();

            }
            this.Connection.Close();

            return true;

        }
        // Creates Create Table Statement and runs it.
        public bool WriteTable(string tableName, Dictionary<string, string>
                                                     tableDefination)
        {
            try
            {
                using (OleDbCommand cmd = new OleDbCommand(
                    this.GenerateCreateTable(tableName, tableDefination), this.Connection))
                {
                    if (this.Connection.State != ConnectionState.Open)
                        this.Connection.Open();
                    cmd.ExecuteNonQuery();
                    return true;
                }
            }
            catch
            {
                return false;
            }
        }
        // Generates Insert Statement and executes it
        public bool AddNewRow(DataRow dr)
        {

            using (OleDbCommand cmd = new OleDbCommand(
                this.GenerateInsertStatement(dr), this.Connection))
            {
                cmd.ExecuteNonQuery();
            }
            return true;
        }
        // Create Table Generation based on Table Defination
        private string GenerateCreateTable(string tableName,
                                           Dictionary<string, string> tableDefination)
        {

            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("CREATE TABLE [{0}](", tableName);
            firstcol = true;
            foreach (KeyValuePair<string, string> keyvalue in tableDefination)
            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;
                sb.AppendFormat("{0} {1}", keyvalue.Key, keyvalue.Value);
            }

            sb.Append(")");
            return sb.ToString();
        }
        //Generates InsertStatement from a DataRow.
        private string GenerateInsertStatement(DataRow dr)
        {
            StringBuilder sb = new StringBuilder();
            bool firstcol = true;
            sb.AppendFormat("INSERT INTO [{0}](", dr.Table.TableName);


            foreach (DataColumn dc in dr.Table.Columns)
            {
                if (!firstcol)
                {
                    sb.Append(",");
                }
                firstcol = false;

                sb.Append(dc.Caption);
            }

            sb.Append(") VALUES(");
            firstcol = true;
            for (int i = 0; i <= dr.Table.Columns.Count - 1; i++)
            {
                if (!object.ReferenceEquals(dr.Table.Columns[i].DataType, typeof(int)))
                {
                    sb.Append("'");
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                    sb.Append("'");
                }
                else
                {
                    sb.Append(dr[i].ToString().Replace("'", "''"));
                }
                if (i != dr.Table.Columns.Count - 1)
                {
                    sb.Append(",");
                }
            }

            sb.Append(")");
            return sb.ToString();
        }


    }
}